﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using DAL;
using CommontLibrary;
using System.Xml;
using System.Xml.Xsl;
public partial class UserControl_Reports_loadReport1a : System.Web.UI.UserControl
{
    #region "Member variables"
    string[] strTruongHocs = {"0+ (PTTH, TC-CĐ Nghề)", "Trung cấp Khối KTế", "Trung cấp ĐTử - CNTT", "Trung cấp ngành #", 
                                "Cao đẳng Khối Ktế", "Cao đẳng Đtử - CNTT", "Cao Đẳng ngành #",
                                "Đại học", "Diploma (Aptech, NIIT)", "Không xác Định Đối Tượng"};
    string[] strLevers = { "L1 lấy được trong tuần", "L1 bàn giao", "L2 được nghiệm thu" };
    string[] strLeverCode = { "L1Tuan", "L1BanGiao", "L1NghiemThu" };
    int[] intTotalMothThuTrong;
    int[] intTotalMothBanGiao;
    int[] intTotalMothNghiemThu;
    DAReport objReport;
    DAKhoaHoc objKhoaHoc;
    #endregion

    private void loadDataAccess()
    {
        SqlConnection sqlConnec = ConnectData.getConnection();
        objReport = new DAReport(sqlConnec);
        objKhoaHoc = new DAKhoaHoc(sqlConnec);
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        loadDataAccess();
        if (!Page.IsPostBack)
        {
            this.calStartDate.SelectedDate = FirstAndLastDay.GetFirstDayOfMonth(DateTime.Now);
            this.calEndDate.SelectedDate = FirstAndLastDay.GetLastDayOfMonth(DateTime.Now);
        }
    }
    public void loadNgayView(ref string strTable, string strNameDay, string strText)
    {
        string strTotal = "\n<td style='font-weight:normal;width:41pt;background-color:#800000;color:#ffffff;'>Tổng</td>";
        if (strNameDay == "SATURDAY")
        {
            strTable += "\n<td align='center' style='font-weight:normal;width:41px;background-color:#800000;color:#ffffff;' >Th7 " + strText + " </td>";
        }
        else if (strNameDay == "SUNDAY")
        {
            strTable += "\n<td align='center' style='font-weight:normal;width:41px;background-color:#800000;color:#ffffff;' >CN " + strText + " </td>";
        }
        else
        {
            strTable += "\n<td align='center' style='font-weight:normal;width:41px;background-color:#800000;color:#ffffff;' >Ngày " + strText + " </td>";
        }
        if (strNameDay == "FRIDAY")
        {
            strTable += strTotal;
        }
    }
    public void loadTruongHoc(ref string strTable)
    {
        //Lay du lieu report1a TuNgay-DenNgay
        DataSet dsData = objReport.loadReport1aAll(this.calStartDate.SelectedDate, this.calEndDate.SelectedDate);
        DataTable dtReport1a = new DataTable();
        if (dsData != null && dsData.Tables[0] != null && dsData.Tables[0].Rows.Count > 0)
        {
            dtReport1a = dsData.Tables[0];
        }
        else
        {
            return;
        }
        //Lay danh sach Khoa hoc
        DataTable dtKhoaHoc = objKhoaHoc.loadKhoaHocAll();
        if (dtKhoaHoc != null)
        {
            DataTable dtReportKhoaHoc = dtReport1a.Clone();
            DataRow[] drReportKhoaHoc;
            //noi dung cua tung row
            foreach (DataRow drKhoaHoc in dtKhoaHoc.Rows)
            {
                dtReportKhoaHoc.Rows.Clear();
                drReportKhoaHoc = dtReport1a.Select("KhoaHocID=" + drKhoaHoc["ID"].ToString());
                foreach (DataRow dr in drReportKhoaHoc)
                {
                    dtReportKhoaHoc.ImportRow(dr);
                }
                strTable += "\n<tr>";
                strTable += "\n<td align='left' style='font-weight:bold;' nowrap='nowrap'>" + drKhoaHoc["Ten"].ToString() + "</td>";
                int intDayTotal = 0;
                int intWeekTotal = 0;
                DateTime dtDatei = this.calStartDate.SelectedDate;
                while (dtDatei.Date <= this.calEndDate.SelectedDate.Date)
                {
                    string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
                    string strText = dtDatei.ToString("dd-MM");
                    intDayTotal = 0;
                    for (int j = 0; j < dtReportKhoaHoc.Rows.Count; j++)
                    {
                        DateTime dtTemp = Convert.ToDateTime(dtReportKhoaHoc.Rows[j]["Ngay"]);
                        if (dtDatei.Date == dtTemp.Date)
                        {
                            intDayTotal += Convert.ToInt32(dtReportKhoaHoc.Rows[j]["L1Tuan"]);
                            intDayTotal += Convert.ToInt32(dtReportKhoaHoc.Rows[j]["L1BanGiao"]);
                            intDayTotal += Convert.ToInt32(dtReportKhoaHoc.Rows[j]["L1NghiemThu"]);
                            intWeekTotal += intDayTotal;
                        }
                    }
                    if (strNameDay == "SATURDAY" || strNameDay == "SUNDAY")
                    {
                        string strData = "&nbsp;";
                        if (intDayTotal > 0)
                        {
                            strData = intDayTotal.ToString();
                        }
                        strTable += "\n<td align='center' style='width:41px;background-color:Yellow;' >" + strData + "</td>";
                    }
                    else
                    {
                        strTable += "\n<td align='right' style='width:41px;' >" + intDayTotal.ToString() + "</td>";
                    }
                    if (strNameDay == "FRIDAY")
                    {
                        strTable += "\n<td align='right' style='width:41px;' >" + intWeekTotal.ToString() + "</td>";
                        intWeekTotal = 0;
                    }
                    dtDatei = dtDatei.AddDays(1);
                }
                strTable += "\n</tr>";
                loadLevel(ref strTable, dtReportKhoaHoc);
            }
        }
    }
    public void loadLevel(ref string strTable, DataTable dtReportKhoaHoc)
    {
        int i = 0;
        //noi dung cua tung row
        int intWeekTotal = 0;
        foreach (string strLever in strLevers)
        {
            DateTime dtDatei = this.calStartDate.SelectedDate;
            strTable += "\n<tr>";
            strTable += "\n<td align='left' style='font-weight:normal;' nowrap='nowrap'>" + strLever + "</td>";
            string strColumName = strLeverCode[i];
            intWeekTotal = 0;
            int intDayTotal = 0;
            int intCheckDate = 0;
            while (dtDatei.Date <= this.calEndDate.SelectedDate.Date)
            {
                intDayTotal = 0;
                int intAmount = 0;
                for (int j = 0; j < dtReportKhoaHoc.Rows.Count; j++)
                {
                    DateTime dtTemp = Convert.ToDateTime(dtReportKhoaHoc.Rows[j]["Ngay"]);
                    if (dtDatei.Date == dtTemp.Date)
                    {
                        intAmount = Convert.ToInt32(dtReportKhoaHoc.Rows[j][strColumName]);
                    }
                }
                //DateTime dateTemp = dateFirstMoth.AddDays(intCheckDate);
                string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
                string strText = dtDatei.ToString("dd-MM");

                if (strNameDay == "SATURDAY" || strNameDay == "SUNDAY")
                {
                    string strData = "&nbsp;";
                    if (intAmount > 0)
                    {
                        strData = intAmount.ToString();
                        intDayTotal += intAmount;
                        intWeekTotal += intAmount;
                    }
                    strTable += "\n<td align='center' style='width:41px;background-color:Yellow;' >" + strData + "</td>";
                }
                else
                {
                    intDayTotal += intAmount;
                    intWeekTotal += intAmount;
                    strTable += "\n<td align='right' style='width:41px;' >" + intAmount.ToString() + "</td>";
                }

                if (i == 0)
                {
                    intTotalMothThuTrong[intCheckDate] += intDayTotal;
                }
                else if (i == 1)
                {
                    intTotalMothBanGiao[intCheckDate] += intDayTotal;
                }
                else if (i == 2)
                {
                    intTotalMothNghiemThu[intCheckDate] += intDayTotal;
                }
                if (strNameDay == "FRIDAY")
                {
                    strTable += "\n<td align='right' style='width:41px;' >" + intWeekTotal.ToString() + "</td>";
                    intWeekTotal = 0;
                } 
                intCheckDate++;
                dtDatei = dtDatei.AddDays(1);
            }
            strTable += "\n</tr>";
            i++;
        }
    }
    public void loadTotalAllMoth(ref string strTable)
    {
        DateTime dtDatei = this.calStartDate.SelectedDate;
        int intTotal = 0;
        int intCheckDate = 0;

        /// Tong L1 trong tuan
        strTable += "\n<tr>";
        strTable += "\n<td align='left' style='font-weight:bold;color:Red;background-color:#c0c0c0;' nowrap='nowrap'>Tổng L1 Lấy được trong tuần</td>";
        while (dtDatei.Date <= this.calEndDate.SelectedDate.Date)
        {
            string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
            string strText = dtDatei.ToString("dd-MM");
            if (strNameDay == "SATURDAY" || strNameDay == "SUNDAY")
            {
                string strData = "";
                if (Convert.ToInt32(intTotalMothThuTrong[intCheckDate]) > 0)
                {
                    intTotal += intTotalMothThuTrong[intCheckDate];
                    strData = intTotalMothThuTrong[intCheckDate].ToString();
                }
                strTable += "\n<td align='center' style='width:41px;background-color:Yellow;' >" + strData + "</td>";
            }
            else
            {
                intTotal += intTotalMothThuTrong[intCheckDate];
                strTable += "\n<td align='right' style='width:41px;color:Red;background-color:#c0c0c0;' >" + intTotalMothThuTrong[intCheckDate].ToString() + "</td>";
            }
            if (strNameDay == "FRIDAY")
            {
                strTable += "\n<td align='right' style='width:41px;background-color:#c0c0c0;' >" + intTotal.ToString() + "</td>"; ;
                intTotal = 0;
            }
            intCheckDate++;
            dtDatei = dtDatei.AddDays(1);
        }
        /// Tong L1 ban giao
        strTable += "\n</tr>\n<tr>";
        strTable += "\n<td align='left' style='font-weight:bold;color:Red;background-color:#c0c0c0;' nowrap='nowrap'>Tổng L1 bàn giao</td>";
        intCheckDate = 0;
        dtDatei = this.calStartDate.SelectedDate;
        while (dtDatei.Date <= this.calEndDate.SelectedDate.Date)
        {
            string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
            string strText = dtDatei.ToString("dd-MM");
            if (strNameDay == "SATURDAY" || strNameDay == "SUNDAY")
            {
                string strData = "";
                if (Convert.ToInt32(intTotalMothBanGiao[intCheckDate]) > 0)
                {
                    intTotal += intTotalMothBanGiao[intCheckDate];
                    strData = intTotalMothBanGiao[intCheckDate].ToString();
                }
                strTable += "\n<td align='center' style='width:41px;background-color:Yellow;' >" + strData + "</td>";
            }
            else
            {
                intTotal += intTotalMothBanGiao[intCheckDate];
                strTable += "\n<td align='right' style='width:41px;color:Red;background-color:#c0c0c0;' >" + intTotalMothBanGiao[intCheckDate].ToString() + "</td>";
            }
            if (strNameDay == "FRIDAY")
            {
                strTable += "\n<td align='right' style='width:41px;background-color:#c0c0c0;' >" + intTotal.ToString() + "</td>"; ;
                intTotal = 0;
            }
            intCheckDate++;
            dtDatei = dtDatei.AddDays(1);
        }
        /// Tong L2 nghiem thu
        strTable += "\n</tr>\n<tr>";
        strTable += "\n<td align='left' style='font-weight:bold;color:Red;background-color:#c0c0c0;' nowrap='nowrap'>Tổng L2 được nghiệm thu</td>";
        intCheckDate = 0;
        dtDatei = this.calStartDate.SelectedDate;
        while (dtDatei.Date <= this.calEndDate.SelectedDate.Date)//(intCheckDate = intStartDate; intCheckDate < intEndDate; intCheckDate++)
        {
            string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
            if (strNameDay == "SATURDAY" || strNameDay == "SUNDAY")
            {
                string strData = "";
                if (Convert.ToInt32(intTotalMothNghiemThu[intCheckDate]) > 0)
                {
                    intTotal += intTotalMothNghiemThu[intCheckDate];
                    strData = intTotalMothNghiemThu[intCheckDate].ToString();
                }
                strTable += "\n<td align='center' style='width:41px;background-color:Yellow;' >" + strData + "</td>";
            }
            else
            {
                intTotal += intTotalMothNghiemThu[intCheckDate];
                strTable += "\n<td align='right' style='width:41px;color:Red;background-color:#c0c0c0;' >" + intTotalMothNghiemThu[intCheckDate].ToString() + "</td>";
            }
            if (strNameDay == "FRIDAY")
            {
                strTable += "\n<td align='right' style='width:41px;background-color:#c0c0c0;' >" + intTotal.ToString() + "</td>"; ;
                intTotal = 0;
            }
            intCheckDate++;
            dtDatei = dtDatei.AddDays(1);
        }
        strTable += "\n</tr>";
    }
    public void InsertRow(DataTable dtDataSource)
    {
        string strErro = "";
        foreach (DataRow dr in dtDataSource.Rows)
        {
            DateTime dtmNgay = Convert.ToDateTime(dr["Ngay"].ToString());
            int intL1Tuan = Convert.ToInt32(dr["L1Tuan"]);
            int intL1BanGiao = Convert.ToInt32(dr["L1BanGiao"]);
            int intL1NghiemThu = Convert.ToInt32(dr["L1NghiemThu"]);
            int intKhoaHocID = Convert.ToInt32(dr["KhoaHocID"]);

            int intChẹck = objReport.insertReport1a(dtmNgay, intL1Tuan, intL1BanGiao, intL1NghiemThu, intKhoaHocID);
            if (intChẹck < 0)
            {
                strErro += dr["ID"].ToString();
            }
        }
    }
    protected void btnReport1a_Click(object sender, EventArgs e)
    {
        this.lblMess.Text = "";
        string strTable = "<table border='1' cellpadding='2' cellspacing='0' align='left' >";
        DateTime dtDatei = this.calStartDate.SelectedDate;
        int intCountDay = 0;
        while (dtDatei.Date <= this.calEndDate.SelectedDate.Date)
        {
            string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
            string strText = dtDatei.ToString("dd/MM");
            if (dtDatei.Date == this.calStartDate.SelectedDate.Date)
            {
                strTable += "\n<tr>";
                strTable += "\n<td align='center' style='font-weight:bold;background-color:#800000;color:#ffffff;' nowrap='nowrap'>Đối tượng</td>";
                loadNgayView(ref strTable, strNameDay, strText);
            }
            else if (dtDatei.Date == this.calEndDate.SelectedDate.Date)
            {
                loadNgayView(ref strTable, strNameDay, strText);
                strTable += "\n</tr>";
            }
            else
            {
                loadNgayView(ref strTable, strNameDay, strText);
            }
            intCountDay++;
            dtDatei = dtDatei.AddDays(1);
        }
        intTotalMothThuTrong = new int[intCountDay];
        intTotalMothBanGiao = new int[intCountDay];
        intTotalMothNghiemThu = new int[intCountDay];

        loadTruongHoc(ref strTable);
        loadTotalAllMoth(ref  strTable);
        strTable += "\n</table>";
        ltrTable.Text = strTable;
    }
    protected void btnInportExcel_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection sqlConnec = ConnectData.getConnection();
            String filename = fileInportExcel.FileName.ToString();
            ExcelHelper.uploadXLFile(fileInportExcel, Server.MapPath("~/OutputFiles/"));
            String[] sheetNames = new String[100];
            sheetNames = ExcelHelper.getExcelSheets(Server.MapPath("~/OutputFiles/") + filename);
            // loop through worksheet names
            foreach (string shtname in sheetNames)
            {
                DataSet ds = new DataSet();
                if (shtname.Equals("Report1a"))
                {
                    ds = ExcelHelper.getXLData(shtname, Server.MapPath("~/OutputFiles/") + filename, "");
                    if (ds != null)
                    {
                        InsertRow(ds.Tables[0]);
                    }
                }
            }
            this.lblMess.Text = "Đồng bộ dữ liệu thành công";
        }
        catch (Exception ex)
        {
            this.lblMess.Text = "Không đồng bộ dữ liệu được: " + ex.Message;
        }
    }
}